package com.edu.hre.common.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;

import com.edu.hre.db.pojo.HreEmpCompensation;
import com.edu.hre.db.pojo.HreEmployee;
import com.edu.hre.db.pojo.HreJobCandidate;
import com.edu.hre.db.service.BaseService;
import com.edu.hre.db.utils.Compare;

public class ExcelProcessor {

	private static final BaseService baseService = BeanFinder.getBean(BaseService.BEAN_NAME, BaseService.class);

	public static int importEmployeeKpi (InputStream file) {
		
		int count = 0;

		try {
			
			HSSFWorkbook workBook = new HSSFWorkbook(file);
			HSSFSheet workSheet = workBook.getSheetAt(0);
			
			Iterator<Row> iteratorRow = workSheet.iterator();
			
			// Next twice before read.
			if (!iteratorRow.hasNext()) {
				return -1;
			}
			iteratorRow.next();
			
			// Now start reading.
			while (iteratorRow.hasNext()) {

				Row row = iteratorRow.next();
				Iterator<Cell> iteratorCell = row.cellIterator();

				// Next twice before read.
				if (!iteratorCell.hasNext()) {
					continue;
				}
				iteratorCell.next();
				
				try {

					// Read Employee ID
					Cell cell = iteratorCell.next();
					int empNumber = (int) cell.getNumericCellValue();
					
					cell = iteratorCell.next();
					int year = (int) cell.getNumericCellValue();
					
					cell = iteratorCell.next();
					int month = (int) cell.getNumericCellValue();
					
					cell = iteratorCell.next();
					int kpi = (int) cell.getNumericCellValue();
					
					HreEmpCompensation empCom = baseService.getFirstPojoAnd(new String[]{"year", "month", "empNumber"}, new int[]{Compare.EQUAL, Compare.EQUAL, Compare.EQUAL}, new Object[]{year, month, empNumber}, HreEmpCompensation.class);
					
					if (empCom != null) {
						empCom.setKpi(kpi);
					} else {
						empCom = new HreEmpCompensation();
						empCom.setEmpNumber(empNumber);
						empCom.setYear(year);
						empCom.setMonth(month);
						empCom.setKpi(kpi);
					}
					
					boolean isOk = baseService.saveOrUpdate(empCom);
					if (isOk) {
						count++;
					}

				} catch (Exception e) {
					continue;
				}
			}
			
			file.close();

			return count;
			
		} catch (Exception e) {
			CommonUtils.println(e.getMessage());
			return count;
		}
	}
	
	@SuppressWarnings("deprecation")
	public static void exportToEmployeeList (HttpServletResponse response) {
		
		HSSFWorkbook book = new HSSFWorkbook();
		HSSFSheet sheet = book.createSheet("Employee List");

		Row first = sheet.createRow(0);
		Cell cell = first.createCell(1);
		cell.setCellValue("");

		Row second = sheet.createRow(1);
		cell = second.createCell(1);
		cell.setCellValue("ID");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(2);
		cell.setCellValue("Last Name");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(3);
		cell.setCellValue("Middle Name");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(4);
		cell.setCellValue("First Name");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(5);
		cell.setCellValue("Nick Name");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(6);
		cell.setCellValue("Date of Birth");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(7);
		cell.setCellValue("Gender");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(8);
		cell.setCellValue("Marital status");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(9);
		cell.setCellValue("ID Card");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(10);
		cell.setCellValue("Place of Issuance");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(11);
		cell.setCellValue("Date of Issuance");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(12);
		cell.setCellValue("Address 1");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(13);
		cell.setCellValue("Address 2");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(14);
		cell.setCellValue("Home Phone");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(15);
		cell.setCellValue("Mobile Phone");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(16);
		cell.setCellValue("Work Phone");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(17);
		cell.setCellValue("Work Email");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(18);
		cell.setCellValue("Other Email");
		cell.setCellStyle(getHeadingCellStyle(book));
		
		List<HreEmployee> empList = baseService.getPojos("isDeleted", Compare.EQUAL, false, HreEmployee.class);
		
		if (empList == null) {
			return;
		}
		
		int count = 2;
		for (HreEmployee emp: empList) {
			
			int empNumber = emp.getEmpNumber();

			String lastName = emp.getEmpLastname();
			if (lastName == null) {
				lastName = "";
			}
			
			String middleName = emp.getEmpMiddleName();
			if (middleName == null) {
				middleName = "";
			}
			
			String firstName = emp.getEmpFirstname();
			if (firstName == null) {
				firstName = "";
			}

			String nickName = emp.getEmpNickName();
			if (nickName == null) {
				nickName = "";
			}

			String dateOfBirth = CommonUtils.dateToString(emp.getEmpBirthday());
			if (dateOfBirth == null) {
				dateOfBirth = "";
			}

			Short gender = emp.getEmpGender();
			if (gender == null || gender > 2) {
				gender = 0;
			}
			String strGender = StringConstants.ALL_GENDERS[gender];

			String marital = emp.getEmpMaritalStatus();
			if (marital == null) {
				marital = "";
			}

			String idCard = emp.getEmpIdCardNum();
			if (idCard == null) {
				idCard = "";
			}

			String placeOfIssuance = emp.getEmpIdCardPlace();
			if (placeOfIssuance == null) {
				placeOfIssuance = "";
			}

			String dateOfIssuance = CommonUtils.dateToString(emp.getEmpIdCardDate());
			if (dateOfIssuance == null) {
				dateOfIssuance = "";
			}
			
			String address1 = emp.getEmpStreet1();
			if (address1 == null) {
				address1 = "";
			}
			
			String address2 = emp.getEmpStreet2();
			if (address2 == null) {
				address2 = "";
			}
			
			String homePhone = emp.getEmpHmTelephone();
			if (homePhone == null) {
				homePhone = "";
			}
			
			String mobilePhone = emp.getEmpMobile();
			if (mobilePhone == null) {
				mobilePhone = "";
			}
			
			String workPhone = emp.getEmpWorkTelephone();
			if (workPhone == null) {
				workPhone = "";
			}
			
			String workEmail = emp.getEmpWorkEmail();
			if (workEmail == null) {
				workEmail = "";
			}
			
			String otherEmail = emp.getEmpOthEmail();
			if (otherEmail == null) {
				otherEmail = "";
			}
			
			// Create row
			Row row = sheet.createRow(count);
			row.createCell(0);
			
			cell = row.createCell(1);
			cell.setCellValue(empNumber + "");
			cell = row.createCell(2);
			cell.setCellValue(lastName);
			cell = row.createCell(3);
			cell.setCellValue(middleName);
			cell = row.createCell(4);
			cell.setCellValue(firstName);
			cell = row.createCell(5);
			cell.setCellValue(nickName);
			cell = row.createCell(6);
			cell.setCellValue(dateOfBirth);
			cell = row.createCell(7);
			cell.setCellValue(strGender);
			cell = row.createCell(8);
			cell.setCellValue(marital);
			cell = row.createCell(9);
			cell.setCellValue(idCard);
			cell = row.createCell(10);
			cell.setCellValue(placeOfIssuance);
			cell = row.createCell(11);
			cell.setCellValue(dateOfIssuance);
			cell = row.createCell(12);
			cell.setCellValue(address1);
			cell = row.createCell(13);
			cell.setCellValue(address2);
			cell = row.createCell(14);
			cell.setCellValue(homePhone);
			cell = row.createCell(15);
			cell.setCellValue(mobilePhone);
			cell = row.createCell(16);
			cell.setCellValue(workPhone);
			cell = row.createCell(17);
			cell.setCellValue(workEmail);
			cell = row.createCell(18);
			cell.setCellValue(otherEmail);
			
			count++;
		}
		
		for (int i = 1; i <= 18; i++) {
			sheet.autoSizeColumn((short) i);
		}
		
		try {

			Calendar cal = Calendar.getInstance();
			int year = cal.get(Calendar.YEAR);
			int month = cal.get(Calendar.MONTH) + 1;
			int day = cal.get(Calendar.DAY_OF_MONTH);
			int hour = cal.get(Calendar.HOUR_OF_DAY);
			int min = cal.get(Calendar.MINUTE);
			int sec = cal.get(Calendar.SECOND);
			
			String fileName = "employees_" + year + "_" + month + "_" + day + "_" + hour + "_" + min + "_" + sec + ".xls";
			
			response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
			response.setContentLength(book.getBytes().length);
			response.setContentType("application/force-download");
			response.getOutputStream().write(book.getBytes());
		} catch (Exception e) {
			CommonUtils.println(e.getMessage());
		}
	}
	
	@SuppressWarnings("deprecation")
	public static void exportToCandidateList (HttpServletResponse response) {
		
		HSSFWorkbook book = new HSSFWorkbook();
		HSSFSheet sheet = book.createSheet("Candidate List");

		Row first = sheet.createRow(0);
		Cell cell = first.createCell(1);
		cell.setCellValue("");

		Row second = sheet.createRow(1);
		cell = second.createCell(1);
		cell.setCellValue("ID");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(2);
		cell.setCellValue("Last Name");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(3);
		cell.setCellValue("Middle Name");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(4);
		cell.setCellValue("First Name");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(5);
		cell.setCellValue("Date of Birth");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(6);
		cell.setCellValue("Gender");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(7);
		cell.setCellValue("Marital Status");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(8);
		cell.setCellValue("ID Card");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(9);
		cell.setCellValue("Place of Issuance");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(10);
		cell.setCellValue("Date of Issuance");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(11);
		cell.setCellValue("Email");
		cell.setCellStyle(getHeadingCellStyle(book));
		cell = second.createCell(12);
		cell.setCellValue("Contact Number");
		cell.setCellStyle(getHeadingCellStyle(book));
		
		List<HreJobCandidate> empList = baseService.getPojos("isDeleted", Compare.EQUAL, false, HreJobCandidate.class);
		
		if (empList == null) {
			return;
		}
		
		int count = 2;
		for (HreJobCandidate can: empList) {
			
			int canId = can.getId();

			String lastName = can.getLastName();
			if (lastName == null) {
				lastName = "";
			}
			
			String middleName = can.getMiddleName();
			if (middleName == null) {
				middleName = "";
			}
			
			String firstName = can.getFirstName();
			if (firstName == null) {
				firstName = "";
			}

			String dateOfBirth = CommonUtils.dateToString(can.getEmpBirthday());
			if (dateOfBirth == null) {
				dateOfBirth = "";
			}

			Integer gender = can.getGender();
			if (gender == null || gender > 2) {
				gender = 0;
			}
			String strGender = StringConstants.ALL_GENDERS[gender];

			String marital = can.getMaritalStatus();
			if (marital == null) {
				marital = "";
			}

			String idCard = can.getIdCardNumber();
			if (idCard == null) {
				idCard = "";
			}

			String placeOfIssuance = can.getIdCardLocationIssued();
			if (placeOfIssuance == null) {
				placeOfIssuance = "";
			}

			String dateOfIssuance = CommonUtils.dateToString(can.getIdCardDateIssued());
			if (dateOfIssuance == null) {
				dateOfIssuance = "";
			}
			
			String email = can.getEmail();
			if (email == null) {
				email = "";
			}
			
			String phone = can.getContactNumber();
			if (phone == null) {
				phone = "";
			}

			// Create row
			Row row = sheet.createRow(count);
			row.createCell(0);
			
			cell = row.createCell(1);
			cell.setCellValue(canId + "");
			cell = row.createCell(2);
			cell.setCellValue(lastName);
			cell = row.createCell(3);
			cell.setCellValue(middleName);
			cell = row.createCell(4);
			cell.setCellValue(firstName);
			cell = row.createCell(5);
			cell.setCellValue(dateOfBirth);
			cell = row.createCell(6);
			cell.setCellValue(strGender);
			cell = row.createCell(7);
			cell.setCellValue(marital);
			cell = row.createCell(8);
			cell.setCellValue(idCard);
			cell = row.createCell(9);
			cell.setCellValue(placeOfIssuance);
			cell = row.createCell(10);
			cell.setCellValue(dateOfIssuance);
			cell = row.createCell(11);
			cell.setCellValue(email);
			cell = row.createCell(12);
			cell.setCellValue(phone);
			
			count++;
		}
		
		for (int i = 1; i <= 12; i++) {
			sheet.autoSizeColumn((short) i);
		}
		
		try {

			Calendar cal = Calendar.getInstance();
			int year = cal.get(Calendar.YEAR);
			int month = cal.get(Calendar.MONTH) + 1;
			int day = cal.get(Calendar.DAY_OF_MONTH);
			int hour = cal.get(Calendar.HOUR_OF_DAY);
			int min = cal.get(Calendar.MINUTE);
			int sec = cal.get(Calendar.SECOND);
			
			String fileName = "candidates_" + year + "_" + month + "_" + day + "_" + hour + "_" + min + "_" + sec + ".xls";
			
			response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
			response.setContentLength(book.getBytes().length);
			response.setContentType("application/force-download");
			response.getOutputStream().write(book.getBytes());
		} catch (Exception e) {
			CommonUtils.println(e.getMessage());
		}
	}
	
	private static CellStyle getHeadingCellStyle (HSSFWorkbook workbook) {

		org.apache.poi.ss.usermodel.CellStyle style = workbook.createCellStyle();
        style.setBorderTop((short) 1); // double lines border
        style.setBorderBottom((short) 1); // single line border

        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        
        return style;
	}
	
	public static void main (String args[]) {
		
		try {
			FileInputStream file = new FileInputStream(new File("/home/thanhpv00921/sample.xls"));
			
			//readExcelFile(file);
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
}
